select * from (
select last_name as "Lastname", first_name as "Firstname",
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) as "Age",
CONCAT( "",demographic_no,"" ) AS "eChart" ,
(select MAX(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%') as "Last appointment",
(select IF((select MAX(a1. appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE '%B%')>DATE("2020-03-15"), "*", "")) as seen,
(select MIN(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status NOT LIKE '%C%' and a.status NOT LIKE '%X%' AND a.appointment_date>NOW()) as "Next appointment",
demo.phone as "Phone",
(select value FROM demographicExt WHERE demographic_no=demo.demographic_no AND key_val="demo_cell" limit 1) as "Cell",
demo.email as "Email"
FROM demographic demo
WHERE demo.patient_status='AC'
AND demo.provider_no LIKE {provider}
AND
(
(("{last_seen}"=""))
OR
(("{last_seen}"!="") AND (select MAX(a1.appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE '%B%')= 0{age_g})
OR
("{age_g}"="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 80)
)
AND(
("{age_l}"!="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) < 0{age_l})
OR
("{age_l}"="")
)
#check if impossible age
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) < 110
AND(
# diagnoses check
("{dx_code}" = "")
OR
(
("{dx_code}" != "") AND
exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in (0{dx_code}))
)
OR
(
("{dx_code}" != "") AND
exists (select b.id from billing_on_item bd, billing_on_cheader1 b where b.demographic_no = demo.demographic_no and b.id = bd.ch1_id and b.status <> 'D' AND TIMESTAMPDIFF(YEAR, DATE(bd.service_date), NOW()) <=2 AND bd.dx in (0{dx_code}))
)
)
order by (case when '{sort}' = 'last_name' then demo.last_name end) asc,
(case when '{sort}' = 'age' then DATE(CONCAT(demo.year_of_birth, "-", demo.month_of_birth, "-", demo.date_of_birth)) end) asc
) as t1 where seen LIKE "{seen}";
select "'%'" as provider_no, 'All Providers' as provider
union
select provider_no, CONCAT(last_name,', ',first_name, ' (',provider_no,')' ) from provider WHERE status='1'
AND provider_type='doctor'
AND ohip_no>1
ORDER BY provider;
Billed OHIP
Not Billed OHIP
ALL
Last Name
Age